<?php

class FUserBean {
	
	public function __construct() {
	// contructor function
	}
	
	
	function getUserInfo($db,$userId){
    	$sql = sprintf("select u.*,c.country_name from ".TBL_CUSTOMER." u ".
					   " LEFT JOIN ".TBL_E_CURRENCY_USER." e ON u.user_id  = e.user_id ".
					   " LEFT JOIN ".TBL_COUNTRY." c ON u.country  = c.country_id 	 ".
					   "WHERE u.user_id = '$userId'");
    	$res = $db->get_row($sql);
    	return $res;
    }
	
	function getECurrencyUser($db,$userId){
    	$sql = sprintf("select e.*,u.account_number from ".TBL_E_CURRENCY." e ".
					   " LEFT JOIN ".TBL_E_CURRENCY_USER." u ON e.id = u.currency_id AND u.user_id = '$userId'");
    	$res = $db->get_results($sql);
    	return $res;
    }
	
	function checkExistCurrencyAccount($db,$userId,$currencyID){
    	$sql = sprintf("select u.user_id from ".TBL_E_CURRENCY_USER." u ".
					   " WHERE u.currency_id  ='$currencyID'  AND u.user_id = '$userId'");
    	$res = $db->get_results($sql);
    	return $res;
    }
	
	public function saveCurrencyUser($db,$user_id,$currency_id,$account_number) {
    	$sql=sprintf(
					"INSERT INTO %s(user_id,currency_id,account_number) " .
					"VALUES('%s','%s','%s')",
    				TBL_E_CURRENCY_USER, 
    				$user_id,
    				$currency_id,
    				$account_number);
		  
        		
    	$res = $db->query($sql);
		$id = -1;
		if($res == true) {	
			$id=$db->insert_id;
			return $id;
		}	
    	else
    		return $id;
    }
	
	public static function updateCurrencyUser($db,$user_id,$currency_id,$account_number) {
    	
    	$sql = sprintf("UPDATE %s SET " .
    			"account_number = '%s' " .
    			"WHERE  user_id =" . $user_id ." AND currency_id = ".$currency_id,
    			TBL_E_CURRENCY_USER,
    			$account_number);
    	$res = $db->query($sql);

    	return $res;
    }
	
	/**
     * Save customer information to database.
     * Customer information is POST values
     * field name is defined in input form
     * See smarty template for detail of name of form's element.
     */
	
	
	public function saveCustomer($db, $info) {
    	$sql=sprintf(
					"INSERT INTO %s(firstname,lastname,contacts,".
					"email,password,city,state,country,createddate) " .
					"VALUES('%s','%s','%s','%s','%s','%s','%s','%s',now())",
    				TBL_CUSTOMER, 
    				$info['firstname'],
    				$info['lastname'],
					$info['contacts'],
					$info['email'],
					crypt($info['password'], '$ntp$'),
					$info['city'],
					$info['state'],
    				$info['country']);
		  
        		
    	$res = $db->query($sql);
		$id = -1;
		if($res == true) {	
			$id=$db->insert_id;
			return $id;
		}	
    	else
    		return $id;
    }
	
	
	public static function updateCustomer($db, $info) {
    	
    	$sql = sprintf("UPDATE %s SET " .
    			"gender = '%s', " .
    			"firstname = '%s', " .
    			"lastname = '%s', " .
    			"dob = '%s', " .
				"delivery_gender = '%s', " .
    			"delivery_firstname = '%s', " .
    			"delivery_lastname = '%s', " .
				"delivery_street_address = '%s', " .
    			"delivery_postcode = '%s', " .
    			"delivery_city = '%s', " .
    			"delivery_state = '%s', " .
    			"delivery_country = '%s', " .
				"billing_gender = '%s', " .
    			"billing_firstname = '%s', " .
    			"billing_lastname = '%s', " .
				"billing_street_address = '%s', " .
    			"billing_postcode = '%s', " .
    			"billing_city = '%s', " .
    			"billing_state = '%s', " .
    			"billing_country = '%s', " .
    			"company = '%s', " .
    			"email = '%s', " .
    			"telephone = '%s', ".
				"cellphone = '%s', ".
    			"fax = '%s', " .
    			"password = '%s', " .
    			"newsletter = '%s' ," .
    			"registered = now() " .
    			"WHERE  id =" . $info['id'],
    			TBL_CUSTOMER,
    			$info['gender'],
    			$info['firstname'],
    			$info['lastname'],
    			$info['dob'],
				$info['delivery_gender'],
    			$info['delivery_firstname'],
    			$info['delivery_lastname'],
				$info['delivery_street_address'],
    			$info['delivery_postcode'],
    			$info['delivery_city'],
    			$info['delivery_state'],
    			$info['delivery_country_id'],
				$info['billing_gender'],
    			$info['billing_firstname'],
    			$info['billing_lastname'],
				$info['billing_street_address'],
    			$info['billing_postcode'],
    			$info['billing_city'],
    			$info['billing_state'],
    			$info['billing_country_id'],
    			$info['company'],
    			$info['email'],
    			$info['telephone'],
				$info['cellphone'],
    			$info['fax'],
    			$info['password'],
    			$info['newsletter']);
    	$res = $db->query($sql);

    	return $res;
    }
	
	
	
	public function activeAccount($db, $id)
    {
		$sql = sprintf("UPDATE %s SET " .
    			"active = '1' " .
    			"WHERE user_id  =" . $id,
    			TBL_CUSTOMER);
		$res = $db->query($sql);		
	}
	
	public function getActiveCustomerById($db, $id) {
    	$sql = sprintf("SELECT c.active FROM ".TBL_CUSTOMER." c ".
						" WHERE c.user_id='$id'");
    	return $db->get_row($sql);
    }
	 /**
     * Get a customer by customer name
     * @param $db is a ezSQL_mysql object
     * @param $name the name want to get other information
     * @return The customer object. 
     * customer object contain fields is same as defined in database  
     */
    public function getCustomerByEmail($db, $email) {
    	$sql = sprintf("SELECT * FROM ".TBL_CUSTOMER." WHERE email='$email' AND active ='1'");
    	return $db->get_row($sql);
    }
	
	public function ExistsByEmail($db, $email) {
    	$sql = sprintf("SELECT id FROM ".TBL_CUSTOMER." WHERE email='$email'");
    	return $db->get_row($sql);
    }
	
	public function updatePassword($db, $email, $pass)
    {
    	$sql = sprintf("UPDATE %s SET " .
    					"password = '%s' " .
    					"WHERE email = '%s'", 
    					TBL_CUSTOMER,
    					mysql_real_escape_string($pass),
    					mysql_real_escape_string($email));		
    	$res = $db->query($sql);
		return $res;
    }
	
	public function getCustomerById($db, $id) {
    	$sql = sprintf("SELECT *,(SELECT t.country_name FROM ".TBL_COUNTRY." t WHERE c.country_id = t.country_id) as country_name, ".
						"(SELECT d.country_name FROM ".TBL_COUNTRY." d WHERE c.delivery_country = d.country_id) as delivery_country_name, ".
						"(SELECT b.country_name FROM ".TBL_COUNTRY." b WHERE c.billing_country = b.country_id) as billing_country_name ".
						"FROM ".TBL_CUSTOMER." c ".
						" WHERE c.id='$id'");
    	$res =  $db->get_row($sql);
		return $res;
    }
	
	
	
	public function getAllEmail($db)
    {
    	$sql = sprintf("SELECT email FROM %s", TBL_CUSTOMER);
    	return $db->get_results($sql);
    }
	
	public function getCustomerEmail($db, $id)
    {
    	$sql = sprintf("SELECT email FROM " . TBL_CUSTOMER . " WHERE id = $id");
    	$customeremail = $db->get_row($sql);
    	return $customeremail->email;
    }
	
	 public function getCustomerPassword($db, $id)
    {
    	$sql = sprintf("SELECT password FROM " . TBL_CUSTOMER . " WHERE id = $id");
    	$pass = $db->get_row($sql);
    	return $pass->password;
    }
	
	
	
	
}
?>
